package qumaimai.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import qumaimai.dao.GoodsDao;
import qumaimai.entity.Goods;
import qumaimai.util.JdbcUtil;

public class GoodsDaoImpl implements GoodsDao {

	@Override
	public List<Goods> findAll() {
		List<Goods> list = new ArrayList<Goods>();
		String sql = "select * from t_goods where is_delete=0 and status=1 limit 8";
		Connection conn = JdbcUtil.getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("id");
				String goodsName = rs.getString("goods_name");
				String goodsDesc = rs.getString("goods_desc");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				String fileName = rs.getString("file_name");
				int status = rs.getInt("status");
				Goods goods = new Goods(id, goodsName, goodsDesc, price, stock, fileName,status);
				list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public Goods findById(int id) {
		Goods goods = null;
		String sql = "select * from t_goods where is_delete=0 and status=1 and id = "+id;
		Connection conn = JdbcUtil.getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				String goodsName = rs.getString("goods_name");
				String goodsDesc = rs.getString("goods_desc");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				String fileName = rs.getString("file_name");
				int status = rs.getInt("status");
				goods = new Goods(id, goodsName, goodsDesc, price, stock, fileName,status);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return goods;
	}

	@Override
	public List<Goods> findAllForAdmin(int page,int size,String name) {
		List<Goods> list = new ArrayList<Goods>();
		int n = (page-1)*size;
		String sql = "";
		if(name==null||name==""){
			sql = "select * from t_goods where is_delete=0 limit "+n+","+size;
		}else{
			sql = "select * from t_goods  where is_delete=0 and goods_name='"+name+"' limit "+n+","+size;
		}
		System.out.println(sql);
		Connection conn = JdbcUtil.getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("id");
				String goodsName = rs.getString("goods_name");
				String goodsDesc = rs.getString("goods_desc");
				double price = rs.getDouble("price");
				int stock = rs.getInt("stock");
				int status = rs.getInt("status");
				String fileName = rs.getString("file_name");
				Goods goods = new Goods(id, goodsName, goodsDesc, price, stock, fileName,status);
				list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int getCount(String name) {
		int n = 0;
		String sql = "";
		if(name==null||name==""){
			sql = "select count(*) from t_goods where is_delete=0";
		}else{
			sql = "select count(*) from t_goods where is_delete=0 and goods_name='"+name+"'";
		}
		Connection conn = JdbcUtil.getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			if(rs.next()){
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return n;
	}

	@Override
	public int edit(Goods goods) {
		String sql = "UPDATE t_goods SET  goods_name='"+goods.getGoodsName()+"', goods_desc='"+goods.getGoodsDesc()+"', price="+goods.getPrice()+", stock="+goods.getStock()+" WHERE id="+goods.getId();
		System.out.println(sql);
		return JdbcUtil.update(sql);
	}

	@Override
	public int del(int id) {
		String sql = "update t_goods SET is_delete=1 where id="+id;
		return JdbcUtil.update(sql);
	}

	@Override
	public int sale(int id) {
		String sql = "update t_goods SET status=abs(status-1) where id="+id;
		return JdbcUtil.update(sql);
	}

	@Override
	public int add(Goods goods) {
		String sql = "INSERT INTO t_goods (goods_name, goods_desc, price, stock, classify_level1_id, classify_level2_id, classify_level3_id, file_name) VALUES ( '"+goods.getGoodsName()+"', '"+goods.getGoodsDesc()+"', "+goods.getPrice()+", "+goods.getStock()+", 548, 654, 655, '"+goods.getFileName()+"')";
		return JdbcUtil.update(sql);
	}

	@Override
	public int changeImg(int id, String filename) {
		String sql = "update t_goods SET file_name='"+filename+"' where id="+id;
		return JdbcUtil.update(sql);
	}
	@Override
	public int updateStock(int id, int num) {
		String sql = "update t_goods SET stock=stock-"+num+" where id="+id;
		return JdbcUtil.update(sql);
	}
	@Override
	public int addStock(int id, int num) {
		String sql = "update t_goods SET stock=stock+"+num+" where id="+id;
		return JdbcUtil.update(sql);
	}



}